package com.xm.dao;

import java.sql.ResultSet;

import java.sql.SQLException;
import java.util.ArrayList;

import com.mysql.jdbc.StringUtils;
import com.xm.entity.Banners;
import com.xm.entity.Product;
import com.xm.uti.DBManager;
import com.xm.uti.QueryDataResult;

/**
 * 产品的持久层的操作
 * 
 * @author 吴雪曼
 *
 */
public class ProductDaoImpl {
	

	/**
	 * 增
	 * 
	 * @param emp
	 * @return
	 */
	public boolean insertPro(Product product) {
		String sql = "insert into product (proname,protype,color,market,price,brand,style,material,zuZhuang,dingzhi,picurl,isShow,model,element,positioning) values('"
				+ product.getProname() + "','" + product.getProtype() + "','" + product.getColor() + "','"
				+ product.getMarket() + "','" + product.getPrice() + "','" + product.getBrand() + "','"
				+ product.getStyle() + "','" + product.getMaterial() + "','" + product.getZuZhuang() + "','"
				+ product.getDingzhi() + "','" + product.getPicurl() + "','" + product.getIsShow() + "','"
				+ product.getModel() + "','" + product.getElement() + "','" + product.getPositioning() + "')";
		System.out.println("执行的sql语句是" + sql);
		int i = DBManager.executeUpdate(sql);
		if (i > 0) {
			return true;
		}
		return false;
	}

	/**
	 * 修改
	 * 
	 * @param emp
	 * @return
	 */
	public boolean updatePro(Product product) {
		System.out.println("产品的数据是" + product);
		String sql = "update product set proname='" + product.getProname() + "',protype='" + product.getProtype()
				+ "',color='" + product.getColor() + "',market='" + product.getMarket() + "',price='"
				+ product.getPrice() + "',brand='" + product.getBrand() + "',style='" + product.getStyle()
				+ "',material='" + product.getMaterial() + "',zuZhuang='" + product.getZuZhuang() + "',dingzhi='"
				+ product.getDingzhi() + "',picurl='" + product.getPicurl() + "',isShow='" + product.getIsShow()
				+ "',model='" + product.getModel() + "',element='" + product.getElement() + "',positioning='"
				+ product.getPositioning() + "'where id =" + product.getId();
		int i = DBManager.executeUpdate(sql);
		if (i > 0) {
			return true;
		}
		return false;
	}

	/**
	 * 删除
	 * 
	 * @param emp
	 * @return
	 */
	public boolean delProduct(int id) {
		String sql = "delete from product where id = " + id;
		int i = DBManager.executeUpdate(sql);
		if (i > 0) {
			return true;
		}
		return false;
	}

	/**
	 * 查列表---多条件分页查询
	 * 
	 * @param name
	 * @return
	 */
	public ArrayList<Product> getProFen(String name, String protype, String minPrice, String maxPrice, String page,String limit) {
		String sql = "select p.* ,prot.typename as typename from product as p left join producttype as prot on p.Protype = prot.id where 1=1";
		//动态拼接sql语句
		if (!StringUtils.isNullOrEmpty(name)) {
			sql += " and proname like '%" + name + "%' ";
		}
		if (!StringUtils.isNullOrEmpty(protype)) {
			sql += " and protype='" + protype + "' ";
		}
		if (!StringUtils.isNullOrEmpty(minPrice)) {
			sql += " and price >= " + minPrice;
		}
		if (!StringUtils.isNullOrEmpty(maxPrice)) {
			sql += " and price <= " + maxPrice;
		}
		sql = sql + " order by id desc";
		if (!StringUtils.isNullOrEmpty(page) && !StringUtils.isNullOrEmpty(limit)) {
			int p = Integer.valueOf(page);
			int c = Integer.valueOf(limit);
			int start = (p - 1) * c;
			sql += " limit " + start + ", " + c;
		}

		System.out.println("执行的sql语句是" + sql);
		QueryDataResult executeQuery = DBManager.executeQuery(sql);
		ArrayList<Product> list = new ArrayList<Product>();
		if (executeQuery != null) {
			ResultSet rs = executeQuery.rs;
			try {
				while (rs.next()) {
					Product e = new Product();
					e.setId(rs.getInt(1));
					e.setProname(rs.getString("Proname"));
					e.setProtype(rs.getInt("Protype"));
					e.setColor(rs.getString("color"));
					e.setMarket(rs.getDouble("market"));
					e.setPrice(rs.getDouble("Price"));
					e.setBrand(rs.getString("Brand"));
					e.setStyle(rs.getString("Style"));
					e.setMaterial(rs.getString("Material"));
					e.setZuZhuang(rs.getString("Zuzhuang"));
					e.setDingzhi(rs.getString("Dingzhi"));
					e.setPicurl(rs.getString("Picurl"));
					e.setIsShow(rs.getString("isShow"));
					e.setModel(rs.getString("model"));
					e.setElement(rs.getString("element"));
					e.setPositioning(rs.getString("positioning"));
					e.setTypename(rs.getString("typename"));
					list.add(e);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				executeQuery.close();
			}
		}
		return list;

	}

	/**
	 * 根据产品名字模糊查
	 * 
	 * @param name
	 * @return
	 */
	public ArrayList<Product> getName(String proname) {
		String sql = "select * from product where proname like '%" + proname + "%'";
		QueryDataResult executeQuery = DBManager.executeQuery(sql);
		ResultSet rs = executeQuery.rs;
		ArrayList<Product> list = new ArrayList<Product>();
		try {
			while (rs.next()) {
				Product e = new Product();
				e.setId(rs.getInt(1));
				e.setProname(rs.getString("Proname"));
				e.setProtype(rs.getInt("Protype"));
				e.setColor(rs.getString("color"));
				e.setMarket(rs.getDouble("market"));
				e.setPrice(rs.getDouble("Price"));
				e.setBrand(rs.getString("Brand"));
				e.setStyle(rs.getString("Style"));
				e.setMaterial(rs.getString("Material"));
				e.setZuZhuang(rs.getString("Zuzhuang"));
				e.setDingzhi(rs.getString("Dingzhi"));
				e.setPicurl(rs.getString("Picurl"));
				e.setIsShow(rs.getString("isShow"));
				e.setModel(rs.getString("model"));
				e.setElement(rs.getString("element"));
				e.setPositioning(rs.getString("positioning"));
				list.add(e);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			executeQuery.close();
		}
		if (list.size() == 0) {// 判断集合中长度是否等于零 则说明没有查到相关信息
			System.out.println("没有查询到相关产品信息");
			return null;
		} else {
			for (int i = 0; i < list.size(); i++) {
				System.out.println(list.get(i));
			}
			return list;
		}
	}

	/**
	 * 首页显示推荐的产品，查前6个推荐内容方法
	 * 
	 * @return
	 */
	public ArrayList<Product> getShouList() {
		String sql = "select * from product where isShow = '是' limit 6";
		System.out.println("执行的sql语句是" + sql);
		QueryDataResult execQuery = DBManager.executeQuery(sql);
		ArrayList<Product> list = new ArrayList<Product>();
		if (execQuery != null) {
			ResultSet rs = execQuery.rs;
			try {
				while (rs.next()) {
					Product e = new Product();
					e.setId(rs.getInt(1));
					e.setProname(rs.getString("Proname"));
					e.setProtype(rs.getInt("Protype"));
					e.setPicurl(rs.getString("Picurl"));
					e.setIsShow(rs.getString("isShow"));
					list.add(e);
				}
			} catch (SQLException e) {
				// TODO: handle exception
				e.printStackTrace();
			} finally {
				execQuery.close();
			}
		}
		System.out.println(list);
		return list;
	}

	/**
	 * 产品页显示推荐的产品，查前8个推荐内容方法
	 * 
	 * @return
	 */
	public ArrayList<Product> getAllList() {
		String sql = "select * from product order by id";
		System.out.println("执行的sql语句是" + sql);
		QueryDataResult execQuery = DBManager.executeQuery(sql);
		ArrayList<Product> list = new ArrayList<Product>();
		if (execQuery != null) {
			ResultSet rs = execQuery.rs;
			try {
				while (rs.next()) {
					Product e = new Product();
					e.setId(rs.getInt(1));
					e.setProname(rs.getString("Proname"));
					e.setProtype(rs.getInt("Protype"));
					e.setColor(rs.getString("color"));
					e.setMarket(rs.getDouble("market"));
					e.setPrice(rs.getDouble("Price"));
					e.setBrand(rs.getString("Brand"));
					e.setStyle(rs.getString("Style"));
					e.setMaterial(rs.getString("Material"));
					e.setZuZhuang(rs.getString("Zuzhuang"));
					e.setDingzhi(rs.getString("Dingzhi"));
					e.setPicurl(rs.getString("Picurl"));
					e.setIsShow(rs.getString("isShow"));
					list.add(e);
				}
			} catch (SQLException e) {
				// TODO: handle exception
				e.printStackTrace();
			} finally {
				execQuery.close();
			}
		}
		System.out.println(list);
		return list;
	}

	/**
	 * 点击某某系列跳转的方法:根据外键id获取商品列表
	 * 
	 * @return
	 */
	public ArrayList<Product> getListByProType(int protype) {
		String sql = "select * from product where Protype= " + protype + " order by id desc";
		System.out.println("执行的sql语句是" + sql);
		QueryDataResult execQuery = DBManager.executeQuery(sql);
		ArrayList<Product> list = new ArrayList<Product>();
		if (execQuery != null) {
			ResultSet rs = execQuery.rs;
			try {
				while (rs.next()) {
					Product e = new Product();
					e.setId(rs.getInt(1));
					e.setProname(rs.getString("Proname"));
					e.setProtype(rs.getInt("Protype"));
					e.setColor(rs.getString("color"));
					e.setMarket(rs.getDouble("market"));
					e.setPrice(rs.getDouble("Price"));
					e.setBrand(rs.getString("Brand"));
					e.setStyle(rs.getString("Style"));
					e.setMaterial(rs.getString("Material"));
					e.setZuZhuang(rs.getString("Zuzhuang"));
					e.setDingzhi(rs.getString("Dingzhi"));
					e.setPicurl(rs.getString("Picurl"));
					e.setIsShow(rs.getString("isShow"));
					e.setModel(rs.getString("model"));
					e.setElement(rs.getString("element"));
					e.setPositioning(rs.getString("positioning"));
					list.add(e);
				}
			} catch (SQLException e) {
				// TODO: handle exception
				e.printStackTrace();
			} finally {
				execQuery.close();
			}
		}
		return list;
	}

	/**
	 * 根据产品自身id编号查询信息，单击产品查询详情方法
	 * 
	 * @param id
	 * @return
	 */
	public ArrayList<Product> getChaDange(int id) {
		String sql = "select * from product where id= " + id;
		System.out.println("执行的sql语句是" + sql);
		QueryDataResult execQuery = DBManager.executeQuery(sql);
		ArrayList<Product> list = new ArrayList<Product>();
		if (execQuery != null) {
			ResultSet rs = execQuery.rs;
			try {
				while (rs.next()) {
					Product e = new Product();
					e.setId(rs.getInt(1));
					e.setProname(rs.getString("Proname"));
					e.setProtype(rs.getInt("Protype"));
					e.setColor(rs.getString("color"));
					e.setMarket(rs.getDouble("market"));
					e.setPrice(rs.getDouble("Price"));
					e.setBrand(rs.getString("Brand"));
					e.setStyle(rs.getString("Style"));
					e.setMaterial(rs.getString("Material"));
					e.setZuZhuang(rs.getString("Zuzhuang"));
					e.setDingzhi(rs.getString("Dingzhi"));
					e.setPicurl(rs.getString("Picurl"));
					e.setIsShow(rs.getString("isShow"));
					e.setModel(rs.getString("model"));
					e.setElement(rs.getString("element"));
					e.setPositioning(rs.getString("positioning"));
					list.add(e);
				}
			} catch (SQLException e) {
				// TODO: handle exception
				e.printStackTrace();
			} finally {
				execQuery.close();
			}
		}
		System.out.println(list);
		return list;
	}

}
